package com.hnkywl.sts.dao.xsgl;

import com.hnkywl.sts.entity.xsgl.Kcbj;
import net.ruixin.orm.hibernate.Page;
import net.ruixin.orm.hibernate.SimpleHibernateTemplate;
import org.hibernate.SQLQuery;
import org.hibernate.transform.AliasToEntityMapResultTransformer;
import org.springframework.stereotype.Repository;

import java.math.BigDecimal;
import java.util.Date;
import java.util.List;
import java.util.Map;

/**
 * Created with IntelliJ IDEA.
 * User: Administrator
 * Date: 13-4-22
 * Time: 下午3:36
 * To change this template use File | Settings | File Templates.
 */
@Repository
public class KcbjDao extends SimpleHibernateTemplate<Kcbj, Long> {
    public Page<Map> findKcbjList(Date bjrq, Long nbgsId, List bjrqList, int pageNo, int pageSize) {
        Page<Map> page = new Page<Map>(pageNo, pageSize, 0);
        String select = "select gg.id \"gg.id\",gg.name \"gg.name\",gg.code \"gg.code\",gg.cz \"gg.cz\",gg.cz||' '||gg.ggxh \"gg.ggxh\", gg.dw \"gg.dw\"," +
                "       cd.id \"cd.id\", kcbj.hdbj \"hdbj\",kcbj.hdscbj \"hdscbj\",kcbj.hdwj \"hdwj\",cd.z \"cd.z\",case when nvl(sum(kh.sl),0)<0 then 0 else nvl(sum(kh.sl),0) end \"kcsl\"," +
                "       case when nvl(sum(kh.kkdsl),0)<0 then 0 else nvl(sum(kh.kkdsl),0) end \"kkdsl\",kcbj.bj \"bj\",kcbj.scbj \"scbj\",kcbj.wj \"wj\",bjgs.id as \"gsId\",kcbj.id as \"id\"," +
                "       bjgs.jc \"bjgs.jc\",kcbj.bjrq \"bjrq\"";
        String from = "from t_biz_kcbj kcbj left join t_sys_gg gg on gg.id = kcbj.gg_id left join t_sys_gsjg bjgs on bjgs.id = kcbj.bjgs_id " +
                "  left join t_biz_kh kh on kh.jtwz_id = gg.id and kh.sbgs_id = bjgs.id and nvl(kh.cd_sjzdz_id, 0) =  nvl(kcbj.cd_sjzdz_id, 0) " +
                "  left join t_sys_sjzdz cd on kcbj.cd_sjzdz_id = cd.id " + " where kcbj.bjrq = :bjrq and hdbj is not null " +
                (nbgsId == null ? "" : " and kcbj.bjgs_id = :bjgsId ") +
                " group by gg.id,gg.name,gg.code,gg.cz,gg.ggxh,gg.dw,cd.id,cd.z,kcbj.id, kcbj.bj,kcbj.scbj,kcbj.wj,kcbj.hdbj,kcbj.hdscbj ,kcbj.hdwj,kcbj.id,bjgs.jc,bjgs.id,kcbj.bjrq order by bjgs.id,gg.code";
        String countQuery = "select nvl(count(count(1)),0) " + from;
        String query = select + from;
        SQLQuery countSQLQuery = getSession().createSQLQuery(countQuery);
        SQLQuery sqlQuery = getSession().createSQLQuery(query);
        if (nbgsId != null) {
            sqlQuery.setLong("bjgsId", nbgsId);
            countSQLQuery.setLong("bjgsId", nbgsId);
        }
        sqlQuery.setDate("bjrq", bjrq);
        countSQLQuery.setDate("bjrq", bjrq);
        sqlQuery.setResultTransformer(AliasToEntityMapResultTransformer.INSTANCE);

        page.setTotalCount(((BigDecimal) countSQLQuery.uniqueResult()).intValue());
        if (page.getTotalCount() != 0) {
            if (page.isFirstSetted())
                sqlQuery.setFirstResult(page.getFirst());
            if (page.isPageSizeSetted())
                sqlQuery.setMaxResults(page.getPageSize());
            page.setResult(sqlQuery.list());
        } else {
            String rqselect = "select max(bjrq) from t_biz_kcbj kcbj where hdbj is not null " + (nbgsId == null ? "" : " and kcbj.bjgs_id = :bjgsId ");
            SQLQuery rqSQLQuery = getSession().createSQLQuery(rqselect);
            if (nbgsId != null) {
                rqSQLQuery.setLong("bjgsId", nbgsId);
            }
            bjrq = (Date) rqSQLQuery.uniqueResult();
            sqlQuery.setDate("bjrq", bjrq);
            countSQLQuery.setDate("bjrq", bjrq);
            page.setTotalCount(((BigDecimal) countSQLQuery.uniqueResult()).intValue());
            if (page.isFirstSetted())
                sqlQuery.setFirstResult(page.getFirst());
            if (page.isPageSizeSetted())
                sqlQuery.setMaxResults(page.getPageSize());
            page.setResult(sqlQuery.list());
        }
        bjrqList.add(bjrq);
        return page;
    }

    //库存报价列表
    public Page<Map> findAllKcbj(Date bjrq, Long nbgsId, int pageNo, int pageSize) {
        Page<Map> page = new Page<Map>(pageNo, pageSize, 0);
        String select = "select gg.id \"gg.id\",gg.name \"gg.name\",gg.code \"gg.code\",gg.cz \"gg.cz\",gg.cz||' '||gg.ggxh \"gg.ggxh\", gg.dw \"gg.dw\"," +
                "       cd.id \"cd.id\", kcbj.hdbj \"hdbj\",kcbj.hdscbj \"hdscbj\",kcbj.hdwj \"hdwj\",cd.z \"cd.z\",case when nvl(sum(kh.sl),0)<0 then 0 else nvl(sum(kh.sl),0) end \"kcsl\"," +
                "       case when nvl(sum(kh.kkdsl),0)<0 then 0 else nvl(sum(kh.kkdsl),0) end \"kkdsl\",kcbj.bj \"bj\",kcbj.scbj \"scbj\",kcbj.wj \"wj\",bjgs.id as \"gsId\",kcbj.id as \"id\"," +
                "       bjgs.jc \"bjgs.jc\",kcbj.bjrq \"bjrq\"";
        String from = "from t_biz_kcbj kcbj left join t_sys_gg gg on gg.id = kcbj.gg_id left join t_sys_gsjg bjgs on bjgs.id = kcbj.bjgs_id " +
                "  left join t_biz_kh kh on kh.jtwz_id = gg.id and kh.sbgs_id = bjgs.id and nvl(kh.cd_sjzdz_id, 0) =  nvl(kcbj.cd_sjzdz_id, 0) " +
                "  left join t_sys_sjzdz cd on kcbj.cd_sjzdz_id = cd.id " + " where kcbj.bjrq = :bjrq " +
                (nbgsId == null ? "" : " and kcbj.bjgs_id = :bjgsId ") +
                " group by gg.id,gg.name,gg.code,gg.cz,gg.ggxh,gg.dw,cd.id,cd.z,kcbj.id, kcbj.bj,kcbj.scbj,kcbj.wj,kcbj.hdbj,kcbj.hdscbj ,kcbj.hdwj,kcbj.id,bjgs.jc,bjgs.id,kcbj.bjrq order by bjgs.id,gg.code";
        String countQuery = "select nvl(count(count(1)),0) " + from;
        String query = select + from;
        SQLQuery countSQLQuery = getSession().createSQLQuery(countQuery);
        SQLQuery sqlQuery = getSession().createSQLQuery(query);
        if (nbgsId != null) {
            sqlQuery.setLong("bjgsId", nbgsId);
            countSQLQuery.setLong("bjgsId", nbgsId);
        }
        sqlQuery.setDate("bjrq", bjrq);
        countSQLQuery.setDate("bjrq", bjrq);
        sqlQuery.setResultTransformer(AliasToEntityMapResultTransformer.INSTANCE);

        page.setTotalCount(((BigDecimal) countSQLQuery.uniqueResult()).intValue());
        if (page.getTotalCount() != 0) {
            if (page.isFirstSetted())
                sqlQuery.setFirstResult(page.getFirst());
            if (page.isPageSizeSetted())
                sqlQuery.setMaxResults(page.getPageSize());
            page.setResult(sqlQuery.list());
        }
        return page;
    }

    //库存报价审核列表，默认与报价相同
    public Page<Map> findAllKcbjSp(Date bjrq, Long nbgsId, int pageNo, int pageSize) {
        Page<Map> page = new Page<Map>(pageNo, pageSize, 0);
        String select = "select gg.id \"gg.id\",gg.name \"gg.name\",gg.code \"gg.code\",gg.cz \"gg.cz\",gg.cz||' '||gg.ggxh \"gg.ggxh\", gg.dw \"gg.dw\"," +
                "       cd.id \"cd.id\", kcbj.bj \"hdbj\",kcbj.scbj \"hdscbj\",kcbj.wj \"hdwj\",cd.z \"cd.z\",case when nvl(sum(kh.sl),0)<0 then 0 else nvl(sum(kh.sl),0) end \"kcsl\"," +
                "       case when nvl(sum(kh.kkdsl),0)<0 then 0 else nvl(sum(kh.kkdsl),0) end \"kkdsl\",kcbj.bj \"bj\",kcbj.scbj \"scbj\",kcbj.wj \"wj\",bjgs.id as \"gsId\",kcbj.id as \"id\"," +
                "       bjgs.jc \"bjgs.jc\",kcbj.bjrq \"bjrq\"";
        String from = "from t_biz_kcbj kcbj left join t_sys_gg gg on gg.id = kcbj.gg_id left join t_sys_gsjg bjgs on bjgs.id = kcbj.bjgs_id " +
                "  left join t_biz_kh kh on kh.jtwz_id = gg.id and kh.sbgs_id = bjgs.id and nvl(kh.cd_sjzdz_id, 0) =  nvl(kcbj.cd_sjzdz_id, 0) " +
                "  left join t_sys_sjzdz cd on kcbj.cd_sjzdz_id = cd.id " + " where kcbj.bjrq = :bjrq " +
                (nbgsId == null ? "" : " and kcbj.bjgs_id = :bjgsId ") +
                " group by gg.id,gg.name,gg.code,gg.cz,gg.ggxh,gg.dw,cd.id,cd.z,kcbj.id, kcbj.bj,kcbj.scbj,kcbj.wj,kcbj.hdbj,kcbj.hdscbj ,kcbj.hdwj,kcbj.id,bjgs.jc,bjgs.id,kcbj.bjrq order by bjgs.id,gg.code";
        String countQuery = "select nvl(count(count(1)),0) " + from;
        String query = select + from;
        SQLQuery countSQLQuery = getSession().createSQLQuery(countQuery);
        SQLQuery sqlQuery = getSession().createSQLQuery(query);
        if (nbgsId != null) {
            sqlQuery.setLong("bjgsId", nbgsId);
            countSQLQuery.setLong("bjgsId", nbgsId);
        }
        sqlQuery.setDate("bjrq", bjrq);
        countSQLQuery.setDate("bjrq", bjrq);
        sqlQuery.setResultTransformer(AliasToEntityMapResultTransformer.INSTANCE);

        page.setTotalCount(((BigDecimal) countSQLQuery.uniqueResult()).intValue());
        if (page.getTotalCount() != 0) {
            if (page.isFirstSetted())
                sqlQuery.setFirstResult(page.getFirst());
            if (page.isPageSizeSetted())
                sqlQuery.setMaxResults(page.getPageSize());
            page.setResult(sqlQuery.list());
        }
        return page;
    }

    public List<Map> findKcbjList(Long nbgsId, Date bjrq) {
        String select = "select gg.id \"gg.id\", gg.name \"gg.name\",gg.code \"gg.code\",gg.cz \"gg.cz\",gg.ggxh \"gg.ggxh\",gg.dw \"gg.dw\",cd.id \"cd.id\",kcbj.hdbj \"hdbj\",kcbj.hdscbj \"hdscbj\",kcbj.hdwj \"hdwj\"," +
                "cd.z \"cd.z\",case when nvl(sum(kh.sl),0)<0 then 0 else nvl(sum(kh.sl),0) end \"kcsl\",case when nvl(sum(kh.kkdsl),0)<0 then 0 else nvl(sum(kh.kkdsl),0) end \"kkdsl\",kcbj.id\"id\",  kcbj.id\"id\",kcbj.bj \"bj\",kcbj.scbj \"scbj\",kcbj.wj \"wj\",kh.sbgs_id as \"gsId\"";
        String from = "from t_biz_kcbj kcbj left join t_sys_gg gg on gg.id = kcbj.gg_id left join t_sys_gsjg bjgs on bjgs.id = kcbj.bjgs_id " +
                "  left join t_biz_kh kh on kh.jtwz_id = gg.id and kh.sbgs_id = bjgs.id and nvl(kh.cd_sjzdz_id, 0) =  nvl(kcbj.cd_sjzdz_id, 0) " +
                "  left join t_sys_sjzdz cd on kcbj.cd_sjzdz_id = cd.id " + " where kcbj.bjrq = :bjrq" +
                (nbgsId == null ? "" : " and kcbj.bjgs_id = :bjgsId ") +
                " group by gg.id,gg.name,gg.code,gg.cz,gg.ggxh,gg.dw,cd.id,cd.z,kcbj.id, kcbj.bj,kcbj.scbj,kcbj.wj,kcbj.hdbj,kcbj.hdscbj ,kcbj.hdwj,kcbj.id,bjgs.jc,bjgs.id,kcbj.bjrq,kh.sbgs_id order by bjgs.id,gg.code";
        String query = select + from;
        SQLQuery sqlQuery = getSession().createSQLQuery(query);
        if (nbgsId != null) {
            sqlQuery.setLong("bjgsId", nbgsId);
        }
        sqlQuery.setDate("bjrq", bjrq);
        sqlQuery.setResultTransformer(AliasToEntityMapResultTransformer.INSTANCE);
        return sqlQuery.list();
    }

    public List<Kcbj> findKcbj(Date bjrq, Long nbgsId) {
        String hql = "from Kcbj where bjgs.id = ? and bjrq= ? and deleted = 0";
        return find(hql, nbgsId, bjrq);
    }

    //修改前将当日已有的全部删掉
    public void delete(Date bjrq, Long bjgs) {
        String deleteSql = "delete from T_BIZ_KCBJ kcbj where bjrq = :bjrq and bjgs_id = :bjgsId";
        SQLQuery query = getSession().createSQLQuery(deleteSql);
        query.setParameter("bjrq", bjrq);
        query.setParameter("bjgsId", bjgs);
        query.executeUpdate();
    }

}
